const express = require('express');
const router = express.Router();
const { pool } = require('../config/db');
const auth = require('../middleware/auth');

// 获取成绩分析数据 - 修改路由路径，移除前缀
router.get('/', auth, async (req, res) => {
  try {
    // 验证用户权限
    if (req.user.role !== 'admin' && req.user.role !== 'teacher' && req.user.role !== 'counselor') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { classId, courseId, term } = req.query;
    
    // 构建查询条件
    const conditions = [];
    const params = [];
    
    if (term) {
      conditions.push('co.semester = ?');
      params.push(term);
    }
    
    if (classId && classId !== '') {
      conditions.push('c.id = ?');
      params.push(classId);
    }
    
    if (courseId && courseId !== '') {
      conditions.push('co.id = ?');
      params.push(courseId);
    }
    
    const whereClause = conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '';
    
    // 获取总体统计数据
    const statsQuery = `
      SELECT 
        COUNT(*) as total,
        AVG(g.score) as avgScore,
        SUM(CASE WHEN g.score >= 60 THEN 1 ELSE 0 END) / COUNT(*) * 100 as passRate,
        SUM(CASE WHEN g.score >= 90 THEN 1 ELSE 0 END) / COUNT(*) * 100 as excellentRate,
        SUM(CASE WHEN g.score < 60 THEN 1 ELSE 0 END) / COUNT(*) * 100 as failRate
      FROM grades g
      JOIN users u ON g.studentId = u.id
      JOIN classes c ON u.classId = c.id
      JOIN courses co ON g.courseId = co.id
      ${whereClause}
    `;
    
    // 修复分数分布数据查询 - 将CASE表达式移到GROUP BY中
    const distributionQuery = `
      SELECT 
        CASE 
          WHEN g.score >= 90 AND g.score <= 100 THEN '90-100'
          WHEN g.score >= 80 AND g.score < 90 THEN '80-89'
          WHEN g.score >= 70 AND g.score < 80 THEN '70-79'
          WHEN g.score >= 60 AND g.score < 70 THEN '60-69'
          WHEN g.score >= 0 AND g.score < 60 THEN '0-59'
        END as scoreRange,
        COUNT(*) as count,
        COUNT(*) / (SELECT COUNT(*) FROM grades g 
                    JOIN users u ON g.studentId = u.id 
                    JOIN classes c ON u.classId = c.id
                    JOIN courses co ON g.courseId = co.id
                    ${whereClause}) * 100 as percentage,
        MIN(
          CASE 
            WHEN g.score >= 90 AND g.score <= 100 THEN 90
            WHEN g.score >= 80 AND g.score < 90 THEN 80
            WHEN g.score >= 70 AND g.score < 80 THEN 70
            WHEN g.score >= 60 AND g.score < 70 THEN 60
            WHEN g.score >= 0 AND g.score < 60 THEN 0
          END
        ) as min,
        MAX(
          CASE 
            WHEN g.score >= 90 AND g.score <= 100 THEN 100
            WHEN g.score >= 80 AND g.score < 90 THEN 89
            WHEN g.score >= 70 AND g.score < 80 THEN 79
            WHEN g.score >= 60 AND g.score < 70 THEN 69
            WHEN g.score >= 0 AND g.score < 60 THEN 59
          END
        ) as max
      FROM grades g
      JOIN users u ON g.studentId = u.id
      JOIN classes c ON u.classId = c.id
      JOIN courses co ON g.courseId = co.id
      ${whereClause}
      GROUP BY scoreRange
      ORDER BY min DESC
    `;
    
    // 执行查询
    const [statsResult, distributionResult] = await Promise.all([
      pool.query(statsQuery, params),
      pool.query(distributionQuery, params.concat(params)) // 因为在子查询中也使用了相同的参数
    ]);
    
    // 返回结果
    res.json({
      statistics: statsResult[0][0],
      distribution: distributionResult[0]
    });
    
  } catch (error) {
    console.error('获取成绩分析数据失败:', error);
    res.status(500).json({ message: '获取成绩分析数据失败' });
  }
});

module.exports = router;